using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Peradigm.Framework.Domain.Security;
using Peradigm.Framework.Persistence;
using Peradigm.Framework.Persistence.Exceptions;
using Peradigm.Framework.Security;

using Liberty.Domain.CaseManagement;
using Liberty.Domain.GrantManagement;
using Liberty.Domain.NoteManagement;
using Liberty.Domain.ServiceMemberManagement;
using Liberty.Domain.Kernel;

namespace Liberty.Persistence.NoteManagement
{
    public class NoteBroker : AbstractBroker
    {
        #region Constants

        private const string SP_NOTES_FOR_CASE = "NoteManagement.NotesForCase";
        private const string SP_NOTES_FOR_GRANT = "NoteManagement.NotesForGrant";
        private const string SP_NOTES_FOR_SERVICE_MEMBER = "NoteManagement.NotesForServiceMember";

        private const string SP_SAVE_CASE_NOTE = "NoteManagement.SaveCaseNote";
        private const string SP_SAVE_GRANT_NOTE = "NoteManagement.SaveGrantNote";
        private const string SP_SAVE_SERVICE_MEMBER_NOTE = "NoteManagement.SaveServiceMemberNote";

        private const string FIELD_PREFIX = "@";

        private const string FIELD_OID = "OID";
        private const string FIELD_ROWVERSION = "Version";
        private const string FIELD_CASE_ID = "CaseID";
        private const string FIELD_GRANT_ID = "GrantID";
        private const string FIELD_GRANT_OID = "GrantOID";
        private const string FIELD_SERVICE_MEMBER_ID = "ServiceMemberID";
        private const string FIELD_NOTES = "Notes";
        private const string FIELD_CREATED_DATE = "CreatedDate";
        private const string FIELD_SERVICE_MAN_OID = "ServiceManOID";
        private const string FIELD_AUTHOR_ID = "AuthorID";

        #endregion

        #region Event Handlers
        #endregion

        #region Methods

        /// <summary>
        /// Gets notes from the database related to the grant and returns that list.
        /// </summary>
        /// <param name="serviceMember"></param>
        /// <returns></returns>
        virtual public List<Note> NotesFor( Case myCase )
        {
            List<Note> notes = new List<Note>();

            SqlCommand cmd = null;

            try
            {
                cmd = this.CommandFor( SP_NOTES_FOR_CASE );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_CASE_ID, myCase.Oid ) );
                notes = this.NotesFrom( cmd );
                myCase.AddNotes( notes );
            }
            finally
            {
                this.Close( cmd );
            }

            return notes;
        }
        
        /// <summary>
        /// Gets notes from the database related to the service menber and returns that list.
        /// </summary>
        /// <param name="serviceMember"></param>
        /// <returns></returns>
        virtual public List<Note> NotesFor( ServiceMember serviceMember )
        {
            List<Note> notes = new List<Note>();

            SqlCommand cmd = null;

            try
            {
                cmd = this.CommandFor( SP_NOTES_FOR_SERVICE_MEMBER );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_SERVICE_MEMBER_ID, serviceMember.Oid ) );

                notes = this.NotesFrom( cmd );

                notes.ForEach( delegate( Note note )
                {
                    serviceMember.AddNote( note );
                } );
            }
            finally
            {
                this.Close( cmd );
            }

            return notes;
        }

        /// <summary>
        /// Gets notes from the database related to the grant and returns that list.
        /// </summary>
        /// <param name="serviceMember"></param>
        /// <returns></returns>
        virtual public List<Note> NotesFor( Grant grant )
        {
            List<Note> notes = new List<Note>();

            SqlCommand cmd = null;

            try
            {
                cmd = this.CommandFor( SP_NOTES_FOR_GRANT );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_GRANT_ID, grant.Oid ) );

                notes = this.NotesFrom( cmd );

                notes.ForEach( delegate( Note note )
                {
                    grant.AddNote( note );
                } );
            }
            finally
            {
                this.Close( cmd );
            }

            return notes;
        }

        public Note SaveNote( Case myCase, Note note )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SP_SAVE_CASE_NOTE );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_OID, note.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_ROWVERSION, note.Version ) );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_CASE_ID, myCase.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_NOTES, note.Text ) );
                if( note.Author != null )
                {
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_AUTHOR_ID, note.Author.GUID ) );
                }

                // Execute and read oid/timestamp
                reader = this.ExecuteReader(cmd);
                if (reader.Read())
                {
                    note.Oid = reader.GetInt64( FIELD_OID );
                    note.Version = reader.GetRowVersion( FIELD_ROWVERSION );
                }
                else
                {
                    throw new ObjectNotSavedException("Unable to save case note");
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return note;
        }

        public Note SaveNote( Grant grant, Note note )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SP_SAVE_GRANT_NOTE );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_OID, note.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_ROWVERSION, note.Version ) );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_GRANT_OID, grant.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_NOTES, note.Text ) );
                if( note.Author != null )
                {
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_AUTHOR_ID, note.Author.Guid ) );
                }
                
                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    note.Oid = reader.GetInt64( FIELD_OID );
                    note.Version = reader.GetRowVersion( FIELD_ROWVERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( "Unable to save grant note" );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return note;
        }

        public Note SaveNote( ServiceMember serviceMember, Note note )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SP_SAVE_SERVICE_MEMBER_NOTE );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_OID, note.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_ROWVERSION, note.Version ) );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_SERVICE_MAN_OID, serviceMember.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_NOTES, note.Text ) );
                if( note.Author != null )
                {
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FIELD_AUTHOR_ID, note.Author.GUID ) );
                }

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    note.Oid = reader.GetInt64( FIELD_OID );
                    note.Version = reader.GetRowVersion( FIELD_ROWVERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( "Unable to save service member note" );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return note;
        }

        #endregion

        #region Properties
        #endregion

        #region Private Methods

        private Note NoteFrom( SafeReader reader )
        {
            long oid = reader.GetInt64( FIELD_OID );
            byte[] version = reader.GetRowVersion( FIELD_ROWVERSION );
            DateTime createdDate = reader.GetDateTime( FIELD_CREATED_DATE );
            string text = reader.GetString( FIELD_NOTES );

            User author = null;
            Guid authorID = reader.GetGuid( FIELD_AUTHOR_ID );
            if( authorID != Guid.Empty )
            {
                SecurityService service = new SecurityService();
                author = service.UserWith( authorID );
            }
            else
            {
                author = new User( 0, "Unknown", "User" );
            }
            return new Note( oid, version, author, createdDate, text );
        }

        private List<Note> NotesFrom( SqlCommand cmd )
        {
            SafeReader reader;
            List<Note> notes;

            reader = null;
            try
            {
                reader = this.ExecuteReader( cmd );
                notes = new List<Note>();
                while( reader.Read() )
                {
                    notes.Add( this.NoteFrom( reader ) );
                }
            }
            finally
            {
                this.Close( reader );
            }

            return notes;
        }

        #endregion

        #region Private Properties
        #endregion

        #region Construction and Finalization
        public NoteBroker( SqlTransaction txn )
            : base( txn )
        {
        }

        public NoteBroker( string cxnString )
            : base( cxnString )
        {
        }
        #endregion

        #region Data Elements
        #endregion
    }
}
